package com.tz.online.order.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;


import com.tz.online.book.service.IBookService;
import com.tz.online.entity.Address;
import com.tz.online.entity.Order;
import com.tz.online.entity.OrderItem;
import com.tz.online.entity.Pageing;
import com.tz.online.entity.User;
import com.tz.online.order.dao.IOrderDao;
import com.tz.online.user.service.IUserService;
import com.tz.online.util.BeanFactory;
import com.tz.online.util.DateUtil;
import com.tz.online.util.JdbcUtils;

@SuppressWarnings("all")
public class OrderDaoImpl implements IOrderDao {
	IUserService us = (IUserService) BeanFactory.getBean("userService");
	IBookService bs = (IBookService) BeanFactory.getBean("bookService");
	@Override
	public void insertAddress(Address a) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;

		Address address = null;

		try {
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = "insert into ONLINE_ADDRESS (area, detailaddres, emailcode, isdefault, reciver, tel, user_id) values ('"
					+ a.getArea()
					+ "', '"
					+ a.getDetailAddres()
					+ "', '"
					+ a.getEmailCode()
					+ "', '"
					+ a.getIsDefault()
					+ "', '"
					+ a.getReciver()
					+ "', '"
					+ a.getTel()
					+ "', '"
					+ a.getUser().getUserId() + "')";
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.释放资源
			JdbcUtils.closeResources(conn, st, rs);
		}
		/*
		 * Session ses = null; Transaction tr = null;
		 * 
		 * try { ses = HibernateUtil.getSession(); tr = ses.getTransaction();
		 * tr.begin(); ses.saveOrUpdate(a); tr.commit(); } catch (Exception e) {
		 * // TODO Auto-generated catch block e.printStackTrace(); }finally{
		 * 
		 * if(ses != null){ ses.close(); } }
		 */
	}

	@Override
	public void insertOrder(Order o) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet set = null;
		Order order = null;
		try {
			conn = JdbcUtils.getConnection();
			conn.setAutoCommit(false);
			String sql = "insert into online_order (createdate,ordernum,orderstatus,address_id,user_id) values (?,?,?,?,?)";
			pst = conn.prepareStatement(sql,new String[]{"orderId"});
			pst.setDate(1, new java.sql.Date(o.getCreateDate().getTime()));
			pst.setString(2, o.getOrderNum());
			pst.setString(3, o.getOrderStatus());
			pst.setLong(4, o.getAddress().getAddressId());
			pst.setLong(5, o.getUser().getUserId());
			pst.executeUpdate();
			pst.clearParameters();
			set = pst.getGeneratedKeys();
			long orderId = 0;
			if(set.next()){
				 orderId = set.getLong(1);
			}
			Set<OrderItem> items = o.getItems();
			if(items!=null){
				pst.close();
				String sql2 = "insert into online_item (allprice,count,book_id,order_id) values (?,?,?,?)";
				pst = conn.prepareStatement(sql2);
				for(OrderItem item : items){
					pst.setDouble(1, item.getAllPrice());
					pst.setInt(2, item.getCount());
					pst.setLong(3, item.getBook().getBookId());
					pst.setLong(4, orderId);
					pst.executeUpdate();
				}
			}
			conn.commit();	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.释放资源
			JdbcUtils.closeResources(conn, pst, set);
		}

		/*
		 * Session ses = null; Transaction tr = null;
		 * 
		 * try { ses = HibernateUtil.getSession(); tr = ses.getTransaction();
		 * tr.begin(); ses.saveOrUpdate(o); tr.commit(); } catch (Exception e) {
		 * // TODO Auto-generated catch block e.printStackTrace(); } finally {
		 * 
		 * if (ses != null) { ses.close(); } }
		 */
	}

	@Override
	public List<Address> selectAddressByPage(User user, int size, int now) {
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		Address address = null;
		List<Address> as = new ArrayList<Address>();
		Pageing paging = new Pageing();
		
		try {
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = "select * from online_address  where user_id= '"+user.getUserId()+"' limit "+now+","+size+"";
			rs = st.executeQuery(sql);
			while(rs.next()){
				address = new Address();
				address.setAddressId(rs.getLong(1));
				address.setArea(rs.getString(2));
				address.setDetailAddres(rs.getString(3));
				address.setEmailCode(rs.getString(4));
				address.setIsDefault(rs.getString(5));
				address.setReciver(rs.getString(6));
				address.setTel(rs.getString(7));
				address.setUser(user);
				
				as.add(address);
				/*paging.setAs(as);
				paging.setPageNow(now);
				paging.setPageSize(size);

				int rowCount = (int) this.getRowCount(user, "online_address");
				paging.setRowCount(rowCount);
				int pageCount = 0;

				if (rowCount % size == 0) {
					pageCount = rowCount / size;
				} else {
					pageCount = rowCount / size + 1;
				}

				paging.setPageCount(pageCount);*/
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.closeResources(conn, st, rs);
		}
		
		return as;
	/*	Session ses = null;
		Transaction tr = null;
		List<Address> as = new ArrayList<Address>();
		Pageing paging = new Pageing();

		try {
			ses = HibernateUtil.getSession();
			tr = ses.getTransaction();
			tr.begin();
			String hql = "from Address b where 1=1 and b.user.userId="
					+ user.getUserId();

			as = ses.createQuery(hql).setMaxResults(size)
					.setFirstResult((now - 1) * size).list();
			paging.setAs(as);
			paging.setPageNow(now);
			paging.setPageSize(size);

			int rowCount = (int) this.getRowCount(user, "Address");
			paging.setRowCount(rowCount);
			int pageCount = 0;

			if (rowCount % size == 0) {
				pageCount = rowCount / size;
			} else {
				pageCount = rowCount / size + 1;
			}

			paging.setPageCount(pageCount);

			tr.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			if (ses != null) {
				ses.close();
			}
		}
		return paging;*/
	}

	@Override
	public List<Order> selectOrderByPage(User user, int size, int now) {
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		Order order = null;
		OrderItem item = new OrderItem();
		Set<OrderItem> items = new HashSet<OrderItem>();
		List<Order> os = new ArrayList<Order>();
		/*Pageing paging = new Pageing();*/
		
		try {
			conn = JdbcUtils.getConnection();
			st = conn.createStatement();
			String sql = "select o.*,i.* from online_order o join online_item i on o.orderId = i.order_id where user_id='"+user.getUserId()+"' limit "+(now-1)+","+size+"";
			rs = st.executeQuery(sql);
			while(rs.next()){
				order = new Order();
				order.setOrderId(rs.getLong(1));
				order.setCreateDate(rs.getDate(2));
				order.setOrderNum(rs.getString(3));
				order.setOrderStatus(rs.getString(4));
				order.setAddress(selectAddressById(String.valueOf(rs.getLong(5))));
				order.setUser(user);
				item.setItemId(rs.getLong(7));
				item.setAllPrice(rs.getDouble(8));
				item.setCount(rs.getInt(9));
				item.setBook(bs.queryBookById(String.valueOf(rs.getLong(10))));
				item.setOrder(order);
				items.add(item);
				order.setItems(items);
				os.add(order);
			/*	paging.setOrders(os);
				paging.setPageNow(now);
				paging.setPageSize(size);

				int rowCount = (int) this.getRowCount(user, "online_order");
				paging.setRowCount(rowCount);
				int pageCount = 0;

				if (rowCount % size == 0) {
					pageCount = rowCount / size;
				} else {
					pageCount = rowCount / size + 1;
				}

				paging.setPageCount(pageCount);*/
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.closeResources(conn, st, rs);
		}
		return os;
		
/*		Session ses = null;
		Transaction tr = null;
		List<Order> os = new ArrayList<Order>();
		Pageing paging = new Pageing();

		try {
			ses = HibernateUtil.getSession();
			tr = ses.getTransaction();
			tr.begin();
			String hql = "from Order b where 1=1 and b.user.userId="
					+ user.getUserId();

			os = ses.createQuery(hql).setMaxResults(size)
					.setFirstResult((now - 1) * size).list();
			paging.setOrders(os);
			paging.setPageNow(now);
			paging.setPageSize(size);

			int rowCount = (int) this.getRowCount(user, "Order");
			paging.setRowCount(rowCount);
			int pageCount = 0;

			if (rowCount % size == 0) {
				pageCount = rowCount / size;
			} else {
				pageCount = rowCount / size + 1;
			}

			paging.setPageCount(pageCount);

			tr.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			if (ses != null) {
				ses.close();
			}
		}
		return paging;*/
	}


	@Override
	public Address selectAddressById(String id) {
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		Address address = null;
		
		try {
			// 2.获取数据库连接
			conn = JdbcUtils.getConnection();
			// 3.创建语句对象
			st = conn.createStatement();
			
			String sql = "select * from online_address where addressid='"+Long.parseLong(id)+"'";
			rs = st.executeQuery(sql);
			if(rs.next()){
				address = new Address();
				address.setAddressId(rs.getLong(1));
				address.setArea(rs.getString(2));
				address.setDetailAddres(rs.getString(3));
				address.setEmailCode(rs.getString(4));
				address.setIsDefault(rs.getString(5));
				address.setReciver(rs.getString(6));
				address.setTel(rs.getString(7));
				address.setUser(getUser(rs.getLong(8)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.closeResources(conn, st, rs);
		}
		/*Session ses = null;
		Transaction tr = null;
		Address a = null;

		try {
			ses = HibernateUtil.getSession();
			tr = ses.getTransaction();
			tr.begin();

			a = (Address) ses.get(Address.class, Long.parseLong(id));

			tr.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			if (ses != null) {
				ses.close();
			}
		}

		return a;*/
		return address;
	}
	//根据Id获取User对象
	private User getUser(long id) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		User user = null;
		
		try {
			// 2.获取数据库连接
			conn = JdbcUtils.getConnection();
			// 3.创建语句对象
			st = conn.createStatement();
			
			String sql = "select * from online_user where userid = '"+id+"'";
			rs = st.executeQuery(sql);
			if(rs.next()){
				user = new User();
				user.setUserId(id);
				user.setAddress(rs.getString(2));
				user.setComparate(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setPassword(rs.getString(5));
				user.setTel(rs.getString(6));
				user.setUsername(rs.getString(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.closeResources(conn, st, rs);
		}
		
		return user;
	}

	@Override
	public long getRowCount(User user, String table) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		long rowCount = 0;
		
		try {
			// 2.获取数据库连接
			conn = JdbcUtils.getConnection();
			// 3.创建语句对象
			st = conn.createStatement();
			
			String sql = "select count(*) from "+table+" where 1=1 and user_id='"+user.getUserId()+"'";
			rs = st.executeQuery(sql);
			if(rs.next()){
				rowCount = rs.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.closeResources(conn, st, rs);
		}
		return rowCount;
	}

}
